package main

import (
	"bytes"
	"errors"
	"fmt"
	_ "github.com/denisenkom/go-mssqldb"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
	//_ "github.com/mattn/go-oci8"
	_ "github.com/mattn/go-sqlite3"
	"regexp"
	"sort"
	"strings"
)

// 只要配置好数据库连接信息，执行这个方法，就能将当前数据库中所有的表，保存到 Schema.DoTables 中。
func (m *schema) getAllTable() {
	res := make([]table, 0)
	if err := m.Select(&res, mysqlTableSql, m.dBName); err != nil {
		panic(err)
	}
	sort.Slice(res, func(i, j int) bool { // 排序, 采用升序，按照表名第一个字母排序，比如 all 表排在 book表前面。
		return strings.ToLower(res[i].Name) < strings.ToLower(res[j].Name) // 将表名全部转换为小写。
	})
	m.doTables = res
	for idx, v := range res { // 每循环一次就从 DoTables 中取出一张表。
		idx++
		columns, _ := m.ColumnInfo(v.Name) // 获取表中所有的列信息
		req := &entityReq{
			Index:        idx,
			TableName:    v.Name,
			TableColumns: columns,
			TableComment: v.Comment,
			TagKeys:      m.tagKeys,
		}
		t := table{
			Name:    m.Capitalize(v.Name),
			Comment: v.Comment,
		}
		list = append(list, t)
		data[v.Name] = req
	}
}

//检查字符串,去掉特殊字符
func (m *schema) checkCharDoSpecial(s string, char byte, regs string) []string {
	reg := regexp.MustCompile(regs)
	var result []string
	if arr := reg.FindAllString(s, -1); len(arr) > 0 {
		buf := bytes.Buffer{}
		for key, val := range arr {
			if val != string(char) {
				buf.WriteString(val)
			}
			if val == string(char) && buf.Len() > 0 {
				result = append(result, buf.String())
				buf.Reset()
			}
			//处理最后一批数据
			if buf.Len() > 0 && key == len(arr)-1 {
				result = append(result, buf.String())
			}
		}
	}
	return strings.Split(strings.Join(result, string(char)), string(char))
}

// ColumnInfo 输入表名，查询该表列的详细信息，并将列的信息保存到 []*Column2Filed 结构体指针切片。
func (m *schema) ColumnInfo(tableName string) (reply []*column2Filed, err error) {
	reply = make([]*column2Filed, 0)
	result, err := m.Find(mysqlColumnSql, tableName, m.dBName)
	if err != nil {
		return
	}
	i := 0
	for _, row := range result { // 遍历结果集，对单个结果依次处理。
		var isPRI bool // 记录主键
		if strings.ToUpper(row["column_key"].(string)) == "PRI" {
			isPRI = true
		}
		oriType := row["data_type"].(string)
		var columnDefault string // 记录列的默认值
		if val, ok := row["column_default"].(string); ok {
			columnDefault = val
		}
		reply = append(reply, &column2Filed{
			Index:           i,
			ColumnName:      row["column_name"].(string),
			ColumnType:      oriType,
			ColumnTypeUpper: strings.ToUpper(oriType),
			ColumnTypeNum:   row["column_type"].(string),
			ColumnComment:   row["column_comment"].(string),
			ColumnDefault:   columnDefault,
			ColumnKey:       isPRI,
			IsNull:          row["is_nullable"].(string),
			FieldName:       m.Capitalize(row["column_name"].(string)),
			FieldType:       m.convert[oriType],
		})
		i++
	}
	return
}

func join(s string) string {
	var b strings.Builder
	if !strings.Contains(s, ".db") {
		b.WriteString(s)
		b.WriteString(".db")
		return b.String()
	}
	return s
}

// initDSN 输入数据库驱动名，返回该驱动的DSN。 // mysql驱动时区: "'Asia/Shanghai'"
//  参考链接 https://studygolang.com/articles/19502
func initDSN(driverName string) (string, error) {
	var str string
	if driverName == "" {
		return "", errors.New("请输入以下字符串中的某一个: mysql，postgres，mssql，sqlite3，oracle，oci8")
	}
	switch driverName {
	case "mysql":
		str = fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s&parseTime=True&loc=Local",
			dbc.Username, dbc.Password, dbc.Host, dbc.Port, dbc.DBName, "utf8mb4")
	case "postgres":
		str = fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
			dbc.Host, dbc.Port, dbc.Username, dbc.Password, dbc.DBName)
	case "mssql":
		str = fmt.Sprintf("server=%s;database=%s;user id=%s;password=%s;port=%d;encrypt=disable",
			dbc.Host, dbc.DBName, dbc.Username, dbc.Password, dbc.Port)
	case "sqlite3":
		s := join(dbc.FilePath)
		str = fmt.Sprintf("%s", s)
	case "oracle", "oci8":
		str = fmt.Sprintf("%s/%s@%s:%d/%s", dbc.Username, dbc.Password, dbc.Host, dbc.Port, dbc.DBName)
	default:
		str = fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s&parseTime=True&loc=Local",
			dbc.Username, dbc.Password, dbc.Host, dbc.Port, dbc.DBName, "utf8mb4")
	}
	return str, nil
}

// initDB 连接数据库，通过返回不同的 *sql.DB 对象来操作不同类型的 SQL数据库。
func initDB(cfg config) (*sqlx.DB, error) {
	var (
		dsn        string
		driverName string
	)
	if cfg.Gen.DSN == "" {
		dsn2, err := initDSN(dbc.DriverName)
		if err != nil || dsn == "" {
			return nil, err
		}
		dsn = dsn2
		driverName = dbc.DriverName
	} else {
		dsn = cfg.Gen.DSN
		driverName = cfg.Gen.DriverName
	}
	conn, err := sqlx.Connect(driverName, dsn)
	if err != nil {
		return nil, err
	}
	return conn, nil
}

func newDB(arg *database) (*sqlx.DB, error) {
	dsn, err := initDSN(arg.DriverName)
	if err != nil {
		return nil, err
	}
	conn, err := sqlx.Connect(arg.DriverName, dsn)
	if err != nil {
		return nil, err
	}
	return conn, nil
}

//Find 查询数据库，这是一个通用方法，支持所有SQL数据库驱动。内部逻辑是使用 *sql.DB 操作数据库
func (m *schema) Find(sql string, args ...interface{}) ([]map[string]interface{}, error) {
	stmt, err := m.DB.Prepare(sql)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	rows, err := stmt.Query(args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	//获取列名称
	var columns []string
	if columns, err = rows.Columns(); err != nil {
		return nil, err
	}
	//新建存储结果变量
	result := make([]map[string]interface{}, 0)
	//数据库列的个数。
	count := len(columns)
	//存储数据库列的值
	values := make([]interface{}, count)
	//扫描变量
	scanValues := make([]interface{}, count)
	for rows.Next() {
		//将带&赋值给scanValues切片,然后全部给Scan方法
		for i := 0; i < count; i++ {
			scanValues[i] = &values[i]
		}
		err = rows.Scan(scanValues...) //赋值给scan
		if err != nil {
			continue
		}
		//新建一个实体,用于存储数据
		entity := make(map[string]interface{})
		for i, field := range columns { // 获取数据库中每一列的列名。
			var v interface{}              //定义一个通用变量
			val := values[i]               //获取一个结果数据
			if b, ok := val.([]byte); ok { //判定是否是切片字节
				v = string(b)
			} else {
				v = val
			}
			entity[field] = v //以字段名为键,存储数据
		}
		//追加到结果集里
		result = append(result, entity)
	}
	return result, nil
}

// ============================================  以下方法仅仅参考，这个项目中并没有使用! ================================================
// 这个变量是给 FindTable 用的
var sqlTableStr = `SELECT table_name tableName,TABLE_COMMENT tableDesc
			FROM INFORMATION_SCHEMA.TABLES 
			WHERE UPPER(table_type)='BASE TABLE'
			AND LOWER(table_schema) = ? 
			ORDER BY table_name asc`

// 获取单个数据库中所有的表和和表的注释。
func (m *schema) FindTable(sql string, tableName string) ([]*table, error) {
	stmt, err := m.Prepare(sql)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	rows, err := stmt.Query(tableName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var ts []*table
	for rows.Next() {
		var t table
		err = rows.Scan(&t.Name, &t.Comment)
		if err != nil {
			return nil, err
		}
		ts = append(ts, &t)
	}
	return ts, nil
}

// 这个变量是给 FindField 用的
var sqlColumnStr = `SELECT COLUMN_NAME fName,column_comment fDesc,DATA_TYPE dataType,
						IS_NULLABLE isNull,IFNULL(CHARACTER_MAXIMUM_LENGTH,0) sLength
			FROM information_schema.columns
			WHERE table_schema = ? AND table_name = ?`

// 获取单个数据库中单个表的所有列信息。
func (m *schema) FindColumn(sql string, args ...interface{}) ([]*column, error) {
	stmt, err := m.Prepare(sql)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	rows, err := stmt.Query(args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var result []*column
	for rows.Next() {
		var c column
		err = rows.Scan(&c.Name, &c.Comment, &c.Type, &c.IsNull, &c.Length)
		if err != nil {
			return nil, err
		}
		result = append(result, &c)
	}
	return result, nil
}

// 该 mysqlSql 变量是给 findAllColumn 用的，其中的别名 必须与 findAllColumn 中的一一对应!
var mysqlSql = `SELECT COLUMN_NAME column_name,COLUMN_COMMENT column_comment,DATA_TYPE data_type,COLUMN_TYPE column_type
						IS_NULLABLE Is_Null,IFNULL(CHARACTER_MAXIMUM_LENGTH,0) column_length
			FROM information_schema.columns
			WHERE table_schema = ? AND table_name = ?`

// findAllColumn 获取指定表的所有列。(该方法思路和 ColumnInfo 差不多)
func (m *schema) findAllColumn(sql, tableName string) (columns []column, err error) {
	res, err := m.Find(sql, m.dBName, tableName)
	columns = make([]column, 0)
	for idx, item := range res { // 获取一列
		idx++
		columns = append(columns, column{
			Index:   idx,
			Name:    item["column_name"].(string),
			Type:    item["data_type"].(string),
			TypeNum: item["column_type"].(string),
			Length:  item["column_length"].(int64),
			IsNull:  item["Is_Null"].(string),
			Comment: item["column_comment"].(string),
		})
	}
	sort.Slice(columns, func(i, j int) bool {
		return columns[i].Index < columns[j].Index
	})
	return
}

func (m *schema) Comment(tableName string) (string, error) {
	rows, err := m.Query(mysqlTableComment, tableName, m.dBName)
	if err != nil {
		return "", err
	}
	var val string
	for rows.Next() {
		rows.Scan(&val)
	}
	return val, nil
}

// 这个方法 还需测试
func (m *schema) ColumnInfo2(tableName string) (reply []*column2Filed, err error) {
	result := make([]*column2Filed, 0)
	err = m.Select(&result, mysqlColumnSql, tableName, m.dBName)
	i := 0
	for _, row := range result { // 遍历结果集，对单个结果依次处理。
		reply = append(reply, &column2Filed{
			Index:           i,
			ColumnName:      row.ColumnName,
			ColumnType:      row.ColumnType,
			ColumnTypeUpper: strings.ToUpper(row.ColumnType),
			ColumnTypeNum:   row.ColumnTypeNum,
			ColumnComment:   row.ColumnComment,
			ColumnDefault:   row.ColumnDefault,
			ColumnKey:       row.ColumnKey,
			IsNull:          row.IsNull,
			FieldName:       m.Capitalize(row.ColumnName),
			FieldType:       m.convert[row.ColumnType],
		})
		i++
	}
	return
}
